package cn.com.lms.business.excel;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelService {

    public List<ExcelSheetDataDTO> readByInputStream(InputStream inputStream) throws IOException {

        List<ExcelSheetDataDTO> excelSheetDataDTOList = new ArrayList<>();
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(inputStream);
            int sheetCount = wb.getNumberOfSheets();
            for (int i = 0; i <= sheetCount; i++) {
                ExcelSheetDataDTO excelSheetDataDTO = new ExcelSheetDataDTO();
                List<List<Object>> allRows = new ArrayList<List<Object>>();
                List<Object> titleData = new ArrayList<>();
                //读取第一个Sheet
                Sheet sheet = wb.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                Row titleRow = sheet.getRow(0);
                if (titleRow != null) {
                    titleData = readLine(titleRow);
                }
                //获取第一行和最后一行
                int maxRowNum = sheet.getLastRowNum();
                int minRowNum = sheet.getFirstRowNum();
                // 从第二行开始读取
                for (int j = minRowNum + 1; j <= maxRowNum; j++) {
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    List<Object> rowData = readLine(row);
                    allRows.add(rowData);
                }
                excelSheetDataDTO.setAllRows(allRows);
                excelSheetDataDTO.setSheetName(sheetName);
                excelSheetDataDTO.setShellTitle(titleData);
                excelSheetDataDTOList.add(excelSheetDataDTO);
            }


        } catch (Exception e) {

        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
            if (wb != null && wb instanceof SXSSFWorkbook) {
                SXSSFWorkbook xssfwb = (SXSSFWorkbook) wb;
                xssfwb.dispose();
            }
        }
        return excelSheetDataDTOList;

    }

    /**
     * 读取每一行的数据
     *
     * @param row
     * @return
     */
    private static List<Object> readLine(Row row) {
        short minColNum = row.getFirstCellNum();
        short maxColNum = row.getLastCellNum();
        List<Object> dataList = new ArrayList<Object>();
        for (short colIndex = minColNum; colIndex < maxColNum; colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                continue;
            }
            CellType cellType = cell.getCellType();
            Object value = null;
            if (CellType.NUMERIC.equals(cellType)) {
                value = cell.getNumericCellValue();
            } else if (CellType.STRING == cellType) {
                value = cell.getStringCellValue();
            } else {
                value = cell.getStringCellValue();
            }
            dataList.add(value);
        }

        return dataList;
    }


    // abc.xls
    public  boolean isXls(String fileName) {
        // (?i)忽略大小写
        if (fileName.matches("^.+\\.(?i)(xls)$")) {
            return true;
        } else if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return false;
        } else {
            throw new RuntimeException("格式不对，请确认是否为excel文件");
        }
    }

    public  List<Map<String, String>> readExcel(String fileName, InputStream inputStream) throws Exception {

        boolean ret = isXls(fileName);
        Workbook workbook = null;
        // 根据后缀创建不同的对象
        try {
            if (ret) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            throw new RuntimeException("请确认excel版本,2007前请使用.xls文件后缀,之后请使用.xlsx文件后缀");
        }

        Sheet sheet = null;
        try {
            sheet = workbook.getSheetAt(0);
        } catch (Exception e) {
            throw new RuntimeException("请按照下载模板格式进行导入");
        }
//        String sheetName = sheet.getSheetName();
//        Sheet sheet = workbook.getSheet("订单明细");
//        System.out.println(sheetName);
        // 得到标题行
        Row titleRow = sheet.getRow(0);

        int lastRowNum = sheet.getLastRowNum();
        int lastCellNum = titleRow.getLastCellNum();

        List<Map<String, String>> list = new ArrayList<>();

        for (int i = 1; i <= lastRowNum; i++) {
            Map<String, String> map = new LinkedHashMap<>();
            Row row = sheet.getRow(i);
            for (int j = 0; j < lastCellNum; j++) {
                try {
                    // 得到列名
                    String key = titleRow.getCell(j).getStringCellValue();
                    Cell cell = row.getCell(j);
                    if (cell == null) continue;
                    CellType cellType = cell.getCellType();
                    switch (cellType) {
                        case NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                map.put(key, cell.getDateCellValue().toString());
                            } else {
                                String value = new BigDecimal(cell.getNumericCellValue()).toString();
                                map.put(key, value);
                            }
                            break;
                        default:
                            map.put(key, cell.getStringCellValue());
                            break;
                    }
                } catch (Exception e) {
                    throw new RuntimeException("请按下载模板格式填写导入");
                }
            }
            list.add(map);
        }
        inputStream.close();
        return list;
    }
}
